#include "ui_bofugui.h"
#include "bofugui.h"
#include "QSqlQuery"

QSqlDriver *driver = QSqlDatabase::database().driver();

/********************************************************************************************/

//5.10.1
int BofuGui::on_pushButton_CreateDB_clicked()
{
    if (!createConnection())
        return 1;

    QSqlQuery query;
    if (!query.exec(QString("create database ").append(ui->lineEdit_NewDatabase->text()))) {   //Query for db-creation
        QMessageBox::information(this, QString("Error"), query.lastError().text());
    } else {
        logDate();
        QString success = "<h2>Done</h2> <p>Database '"+ui->lineEdit_NewDatabase->text()+"' has been created!</p>";
        QMessageBox::information(this, QString("Database Created"), QString::fromUtf8(success.toUtf8().data()));
        writeToLog("echo \"Database created\" >> "+dir.absolutePath()+"/"+korpusname+".log");
        commandexec = system(command.toAscii().data());
    }

    if (!query.exec(QString("GRANT   ALL   on ").append(ui->lineEdit_NewDatabase->text()).append(".* to ").append(ui->lineEdit_Username->text()).append("@").append(ui->lineEdit_Server->text()).append(" identified by '").append(ui->lineEdit_Password->text()).append("'")))   //Query for Grant rights
    {
        QMessageBox::information(this, QString("Error"), query.lastError().text());
    } else {
        QString success = "<h2>Done</h2> <p>The Rights for '"+ui->lineEdit_NewDatabase->text()+"' have been given to '"+ui->lineEdit_Username->text()+"'!</p>";
        QMessageBox::information(this, QString("Database Created"), QString::fromUtf8(success.toUtf8().data()));
        logDate();
        writeToLog("echo \"Rights for Database given\" >> "+dir.absolutePath()+"/"+korpusname+".log");
        scriptDone();

    }
    return 0;
}


bool BofuGui::createConnection()
{
    QSqlDatabase dbroot = QSqlDatabase::addDatabase("QMYSQL");
    dbroot.setHostName(ui->lineEdit_Server->text());
    dbroot.setPort(ui->spinBox_Port->value());
    dbroot.setUserName(ui->lineEdit_SQLAdmin->text());
    dbroot.setPassword(ui->lineEdit_AdminPassword->text());
    if (!dbroot.open()) {
        QMessageBox::critical(0, QObject::tr("Database Error"),
                              dbroot.lastError().text());
        return false;
    }
    writeSettings();

    return true;
}

/********************************************************************************************/
//5.10.2

int BofuGui::on_pushButton_insertTables_clicked()
{

    if (ui->lineEdit_Username->text().isEmpty() || ui->lineEdit_Password->text().isEmpty()){
        QMessageBox::warning(this, (QString::fromUtf8("Error")),
                             QString::fromUtf8("<h2>Missing information</h2>"
                                               "<p>Please fill in Databasename, BOSS-user and User-Password.</p>""<p>Also make sure, that you have created the SQL-Scripts at <b>-5.10.2- SQL-Scripts:</b>.</p>"));
        return 1;

    }else{
        if (!createUserConnection())
            return 1;

        logDate();
        QSqlQuery query;
        QString queryStr;

        QString* inFiles[3] = {new QString(""+dir.absolutePath()+"/sql/create_inv_tables.sql"), new QString(""+dir.absolutePath()+"/sql/create_phon_table.sql"),
                               new QString(""+dir.absolutePath()+"/sql/create_trans_tables.sql")};
        QString* logText[3] = {new QString("Inventory tables"), new QString("Phon tables"), new QString("Trans tables")};
        for (int i=0; i< 3; i++){
            queryStr="";
            QFile in(inFiles[i]->toUtf8());  //Examplefile

            if ( in.open(QIODevice::ReadOnly)) {
                // file opened successfully
                QTextStream instream( &in );        // use a text stream
                // until end of file...
                while ( !instream.atEnd()) {
                    // read and parse a line
                    queryStr = queryStr.append(instream.readLine());
                }
                // Close the files
                in.close();
            }
            if (!query.exec(queryStr)) {   //Query for table-creation
                QMessageBox::information(this, QString("Error"), query.lastError().text());
            } else {
                writeToLog("echo \""+logText[i]->toUtf8()+" created\" >> "+dir.absolutePath()+"/"+korpusname+".log");
            }

        }
        QString success = "<h2>Done</h2> <p>All Tables have been created!</p>";
        QMessageBox::information(this, QString("Tables Created"), QString::fromUtf8(success.toUtf8().data()));
        scriptDone();

        //qDebug(queryStr.toUtf8().data());
    }

    return 0;
}

int BofuGui::on_pushButton_deleteTables_clicked()
{
    if (ui->lineEdit_Username->text().isEmpty() || ui->lineEdit_Password->text().isEmpty()){
        QMessageBox::warning(this, (QString::fromUtf8("Error")),
                             QString::fromUtf8("<h2>Missing information</h2>"
                                               "<p>Please fill in Databasename BOSS-user and User-Password.</p>""<p>Also make sure, that you have created the SQL-Scripts at <b>-5.10.2- SQL-Scripts:</b>.</p>"));
        return 1;

    }else{
        if (!createUserConnection())
            return 1;

        logDate();
        QSqlQuery query;
        QString queryStr;

        QString* inFiles[3] = {new QString(""+dir.absolutePath()+"/sql/delete_inv_tables.sql"),
                               new QString(""+dir.absolutePath()+"/sql/delete_phon_table.sql"), new QString(""+dir.absolutePath()+"/sql/delete_trans_tables.sql")};
        QString* logText[3] = {new QString("create_inv_tables.sql"), new QString("create_phon_table.sql"), new QString("create_trans_tables.sql")};
        for (int i=0; i< 3; i++){
            queryStr="";
            QFile in(inFiles[i]->toUtf8());
            if ( in.open(QIODevice::ReadOnly)) {
                // file opened successfully
                QTextStream instream( &in );        // use a text stream
                // until end of file...
                while ( !instream.atEnd()) {
                    // read and parse a line
                    queryStr = queryStr.append(instream.readLine());
                }
                // Close the files
                in.close();
            }
            if (!query.exec(queryStr)) {   //Query for table-delete
                QMessageBox::information(this, QString("Error"), query.lastError().text());
            } else {

                writeToLog("echo \""+logText[i]->toUtf8()+" deleted\" >> "+dir.absolutePath()+"/"+korpusname+".log");
            }
        }
        QString success = "<h2>Done</h2> <p>All Tables have been deleted!</p>";
        QMessageBox::information(this, QString("Tables deleted"), QString::fromUtf8(success.toUtf8().data()));
        scriptDone();
    }
    return 0;
}

bool BofuGui::createUserConnection()
{
    QSqlDatabase dbuser = QSqlDatabase::addDatabase("QMYSQL");
    dbuser.setHostName(ui->lineEdit_Server->text());
    dbuser.setPort(ui->spinBox_Port->value());
    dbuser.setDatabaseName(ui->lineEdit_NewDatabase->text());
    dbuser.setUserName(ui->lineEdit_Username->text());
    dbuser.setPassword(ui->lineEdit_Password->text());
    if (!dbuser.open()) {
        QMessageBox::critical(0, QObject::tr("Database Error"),
                              dbuser.lastError().text());
        return false;
    }

    writeSettings();
    return true;
}

/********************************************************************************************/
//5.10.3

void BofuGui::on_pushButton_blfxml2db_clicked()
{
    if (ui->lineEdit_Username->text().isEmpty() || ui->lineEdit_Password->text().isEmpty() || ui->lineEdit_NewDatabase->text().isEmpty() || ui->lineEdit_LanguageCode->text().isEmpty()){
        QMessageBox::warning(this, (QString::fromUtf8("Error")),
                             QString::fromUtf8("<h2>Missing information</h2>"
                                               "<p>Please fill in Databasename, Languagecode, BOSS-user and User-Password.</p>""<p>Also make sure, that you have a filelist.</p>"));
    } else{
        ui->statusBar->showMessage(tr("Adding XML to db...please wait!!!"),5000);
        logDate();
        command = "cd "+dir.absolutePath()+"; blfxml2db -list filelist.txt -xdir xml/ -db "+ui->lineEdit_NewDatabase->text()+" -cf "+ui->lineEdit_LanguageCode->text()+"-u "+ui->lineEdit_Username->text()+" -p "+ui->lineEdit_Password->text()+" -sql sql/ >> "+korpusname+".log 2>&1";
        commandexec = system(command.toAscii().data());
        writeToLog("echo \"XML insert into db\" >> "+dir.absolutePath()+"/"+korpusname+".log");
        scriptDone();
    }

}

/********************************************************************************************/
//5.10.4

//open exception-lexicon
void BofuGui::on_pushButton_editLexicon_clicked()
{
    command=("cd "+dir.absolutePath()+"; touch exception_lexicon.txt");
    commandexec = system(command.toAscii().data());
    openURL(QUrl( dir.path().append("/exception_lexicon.txt") ));
}

void BofuGui::on_pushButton_importLexicon_clicked()
{
    QString sourceFile = QFileDialog::getOpenFileName(this, tr("Load Lexicon file"), QDir::homePath());
    if (!sourceFile.isEmpty()){

        ui->statusBar->showMessage(tr("Exception lexicon loaded"),5000);
        logDate();
        command = "cp " +sourceFile+" "+dir.absolutePath()+"/exception_lexicon.txt";
        commandexec = system(command.toAscii().data());
        writeToLog("echo \"Exception Lexicon loaded\" >> "+dir.absolutePath()+"/"+korpusname+".log");
        scriptDone();
        check_existing_files();
    }
}

void BofuGui::on_pushButton_insertLexicon_clicked()
{
    if (ui->lineEdit_Username->text().isEmpty() || ui->lineEdit_Password->text().isEmpty() || ui->lineEdit_NewDatabase->text().isEmpty() || ui->lineEdit_LanguageCode->text().isEmpty()){
        QMessageBox::warning(this, (QString::fromUtf8("Error")),
                             QString::fromUtf8("<h2>Missing information</h2>"
                                               "<p>Please fill in Databasename, Languagecode, BOSS-user and User-Password.</p>""<p>Also make sure, that you have a filelist.</p>"));
    } else{
        ui->statusBar->showMessage(tr("Adding Lexicon to db...please wait!!!"),5000);
        logDate();
        command = "cd "+dir.absolutePath()+"; blf2db -lex exception_lexicon.txt -u "+ui->lineEdit_Username->text()+" -p "+ui->lineEdit_Password->text()+" -db "+ui->lineEdit_NewDatabase->text()+" >> "+korpusname+".log 2>&1";
        commandexec = system(command.toAscii().data());
        writeToLog("echo \"Lexicon inserted into db\" >> "+dir.absolutePath()+"/"+korpusname+".log");
        scriptDone();
    }

}
